package com.bcbim.common.excel;

import com.bcbim.common.excel.annotation.ExcelColumn;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.*;
import java.lang.reflect.Field;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.stream.Collectors;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

/**
 * excel 工具类
 *
 * @author wxc
 * @version V 1.0.0
 * @date 2021/8/30
 **/
public class ExcelUtils {
    // excel 文件类型
    private final static String XLS = "xls";
    private final static String XLSX = "xlsx";

    /**
     * 通过类的字节码文件, 和相同类集合文件将数据存储到workbook对象中
     *
     * @param collect 需要导入到excel中的集合
     * @param cls     需要导出的类的class文件
     * @return org.apache.poi.ss.usermodel.Workbook
     * @author wxc
     * @date 2021/8/30  20:14
     */
    public static <T> Workbook writeExcel(List<T> collect, Class<T> cls) {
        // 获取全部属性数组
        Field[] fields = cls.getDeclaredFields();

        // 过滤不存在ExcelColumn注解的属性,并将过滤完后的属性进行排序后获取属性列表
        List<Field> fieldList = Arrays.stream(fields)
                .filter(
                        field -> {
                            ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
                            if (annotation != null && annotation.index() > 0) {
                                field.setAccessible(true);
                                return true;
                            }
                            return false;
                        }
                ).sorted(Comparator.comparing(field -> {
                    int index = 0;
                    ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
                    if (annotation != null) {
                        index = annotation.index();
                    }
                    return index;
                }))
                .collect(Collectors.toList());

//
        // 用来记录第几列(row)
        int rowIndex = 0;

        // 设置excel文件的标题,通过fieldList来设置
        XSSFWorkbook wb = new XSSFWorkbook();
        XSSFSheet sheet = wb.createSheet("sheet");
        XSSFRow row = sheet.createRow(rowIndex);
        rowIndex++;
        // 遍历属性集合,并设置标题
        for (int i = 0; i < fieldList.size(); i++) {
            String columnName = "";
            Field field = fieldList.get(i);
            ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
            if (annotation != null) {
                columnName = annotation.value();
            }
            Cell cell = row.createCell(i);
            // 设置样式
            CellStyle cellStyle = wb.createCellStyle();
            Font font = wb.createFont();
            font.setBoldweight(Font.BOLDWEIGHT_NORMAL);
            cellStyle.setFont(font);
            cell.setCellStyle(cellStyle);
            cell.setCellValue(columnName);
        }

        // 遍历元素,将数据存入workbook
        if (ObjectUtils.isNotEmpty(collect)) {

            for (T t : collect) {
                XSSFRow row1 = sheet.createRow(rowIndex++);
                // 用来记录第几行(cell)
                int cellIndex = 0;
                try {
                    for (int i = 0; i < fieldList.size(); i++) {
                        Field field = fieldList.get(i);
                        String o = (String) field.get(t);
                        if (StringUtils.isEmpty(o)) {
                            o = "暂无数据";
                        }
                        row1.createCell(i).setCellValue(o);
                        cellIndex++;
                    }
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                }
            }
            // 冻结窗口,锁定列，不让列数随滚动条滚动
            sheet.createFreezePane(0, 1, 0, 1);
        }
        return wb;
    }

    // 将workbook输出为文件
    public static void buildFileByExcel(Workbook workbook, String dirName, String path) {
        // 判断目录是否存在，不存在生成目录
        File dirFile = new File(dirName);
        if (!dirFile.exists()) {
            dirFile.mkdirs();
        }

        File file = new File(path);
        if (file.exists()) {
            file.delete();
        }
        try {
            workbook.write(new FileOutputStream(file));
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    // 将文件压缩，并转化成byte数组
    public static byte[] compressedFiles(List<String> list) {
        ZipOutputStream zos = null;
        ByteArrayOutputStream fos = null;
        try {
            fos = new ByteArrayOutputStream();
            zos = new ZipOutputStream(fos);
            try {
                InputStream input = null;
                for (String fileName : list) {
                    try {
                        input = new FileInputStream(fileName);
                        File files = new File(fileName);
                        ZipEntry zipEntry = new ZipEntry(files.getName());
                        //通过ZipOutputStream将内容写入到zip格式的文件中。期间如要需要加入什么文件。通过ZipEntry来添加。
                        zos.putNextEntry(zipEntry);    // 创建压缩的子目录
                        int temp = 0;
                        while ((temp = input.read()) != -1) { // 读取内容
                            zos.write(temp); // 压缩输出
                        }
                    } finally {
                        try {
                            if (null != input) {
                                input.close();
                            }
                        } catch (IOException e) {
                            e.printStackTrace();
                        }
                    }
                }
            } finally {
                try {
                    if (null != zos) {
                        zos.close();
                    }
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            return fos.toByteArray();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            // 关闭流
            try {
                if (null != fos) fos.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    // 将字节数组输出为文件
    public static void buildFileByByte(byte[] bufferOf, String dirName, String path) {
        // 判断目录是否存在，不存在生成目录
        File dirFile = new File(dirName);
        if (!dirFile.exists()) {
            dirFile.mkdirs();
        }

        BufferedOutputStream outStream = null;
        try {
            outStream = new BufferedOutputStream(new FileOutputStream(path)); //文件输出流将数据写入文件
            outStream.write(bufferOf);
        } catch (IOException io) {
            io.printStackTrace();
        } finally {
            try {
                if (null != outStream) {
                    outStream.close();
                }
            } catch (IOException io) {
                io.printStackTrace();
            }
        }
    }

    // 递归删除删除临时文件
    public static void deleteFile(File file) {
        // 首先判断文件是否存在(存在则不用删除)
        if (!file.exists()) {
            return;
        }
        //如果文件只是单纯的文件，则直接delete
        if (file.isFile()) {
            file.delete();
        } else {
            // 如果文件是为目录(为目录的话是如果底下存在文件是不能删除的,调用自身方法递归删除文件,目录下面可能存在子目录)
            File[] files = file.listFiles();
            for (File file1 : files) {
                deleteFile(file1);
            }
            file.delete();
        }
    }

    //判断文件类型是否为xls(文件类型是否合法)
    public static boolean checkFile(MultipartFile files) {
        String filename = files.getOriginalFilename();
        if (filename.endsWith(XLS) || filename.endsWith(XLSX)) {
            return true;
        }
        return false;
    }

    // 通过文件名称获取workbook对象
    public static Workbook getWorkBook(MultipartFile file) {
        //获得文件名
        String fileName = file.getOriginalFilename();

        //创建Workbook工作薄对象，表示整个excel
        Workbook workbook = null;
        try {
            //获取excel文件的io流
            InputStream is = file.getInputStream();
            if (fileName.endsWith(XLS)) {
                //2003
                workbook = new HSSFWorkbook(is);
            } else if (fileName.endsWith(XLSX)) {
                //2007
                workbook = new XSSFWorkbook(is);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return workbook;
    }

    // 根据实体类中的注解的对应excel的字段的列号
    public static Map<String, String> getDeclaredFieldsInfo(Class<?> cla, Sheet sheet) {
        Map<String, String> map = new HashMap();
        //获得第一行
        Row row = sheet.getRow(0);
        if (row != null) {
            //获得当前行的开始列
            int firstCellNum = row.getFirstCellNum();
            //获得当前行的列数
            int lastCellNum = row.getPhysicalNumberOfCells();
            Field[] fields = cla.getDeclaredFields();
            //循环当前行
            for (int cellNum = firstCellNum; cellNum <= lastCellNum; cellNum++) {
                Cell cell = row.getCell(cellNum);
                String cellValue = String.valueOf(cell);
                if (!StringUtils.isEmpty(cellValue)) {
                    int finalCellNum = cellNum;
                    Arrays.stream(fields).forEach(field -> {
                        ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
                        if (null != annotation && Objects.equals(cellValue, annotation.value())) {
                            map.put(field.getName(), finalCellNum + "");
                        }
                    });
                }
            }
        }
        return map;
    }

    // 时间类型的转化
    public static String localDateTimeFormat(LocalDateTime now, String patten) {
        DateTimeFormatter df = DateTimeFormatter.ofPattern(patten);
        return df.format(now);
    }

    public static String getCellValue(Cell cell) {

        String cellValue = "";
        if (cell == null) {
            return cellValue;
        }
        //把数字当成String来读，避免出现1读成1.0的情况
        if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            cell.setCellType(Cell.CELL_TYPE_STRING);
        }
        //判断数据的类型
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC: //数字
                cellValue = String.valueOf(cell.getNumericCellValue());
                break;
            case Cell.CELL_TYPE_STRING: //字符串
                cellValue = String.valueOf(cell.getStringCellValue());
                break;
            case Cell.CELL_TYPE_BOOLEAN: //Boolean
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_FORMULA: //公式
                cellValue = String.valueOf(cell.getCellFormula());
                break;
            case Cell.CELL_TYPE_BLANK: //空值
                cellValue = "";
                break;
            case Cell.CELL_TYPE_ERROR: //故障
                cellValue = "非法字符";
                break;
            default:
                cellValue = "未知类型";
                break;
        }
        return cellValue;
    }
}
